package UserDatabase;

import java.awt.HeadlessException;
import java.sql.*;

import javax.swing.JOptionPane;
import javax.swing.JPanel;

import com.mysql.cj.jdbc.result.ResultSetMetaData;

public class CardDatabase {

	Connection connect = null;
	Statement stem = null;
	ResultSet rs =null;
	
	String SQL = "";
	String columnName[];
	String record[][];
	int columnCount,recordAmount;
	
	public Connection getConnection() {
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/property_system","root","yang2532661314");
			//System.out.println(connect);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connect;
	}
	
	//通用查询
			
	public void setSQL(String SQL) {
	
		this.SQL = SQL.trim();		
	}

	public String[] getColumnName() {
			
		return columnName;	
	}
					
	public String[][] getRecord(){
		
		startQuery();
				
		return record;
	
	}
	
	public void startQuery() {

		connect  = getConnection();
				
		try {
				
			stem = connect.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
					
			rs = stem.executeQuery(SQL);
					
			ResultSetMetaData metadata = (ResultSetMetaData) rs.getMetaData();
					
			columnCount = metadata.getColumnCount();
			
			columnName = new String[columnCount];
					
			for(int i=1;i<=columnCount;i++) {
					
				columnName[i-1] = metadata.getColumnName(i);
						
			}
					
			rs.last();
					
			recordAmount = rs.getRow();
					
			record = new String[recordAmount][columnCount];
					
			int i = 0;
					
			rs.beforeFirst();
					
			while(rs.next()) {
				
				for(int j=1;j<=columnCount;j++) {
					record[i][j-1] = rs.getString(j);
				}		
				
				i++;	
			}
			
			closegdbc();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();	
		}
	}
			
	
	//购买一卡通
	
	public void addCard(int id,int money) {
		
		connect = getConnection();

		try {
			stem = connect.createStatement();
			stem.executeUpdate("UPDATE Card set 剩余金额 = 剩余金额+"+money+" where Id = "+id+" ");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}                               
	
		closegdbc();
		
	}
	
	//关闭数据库
			
	public void closegdbc() {		
		
		try {
			if(connect!=null) {
				connect.close();		
			}
			if(rs!=null) {
				rs.close();		
			}
			if(stem!=null) {
				stem.close();		
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();	
		}		
	}	
}


